/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.RolDao;
import com.tesam.gedeon.entity.Rol;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author joelpacheco
 */
@Repository
public class RolDaoImpl extends SimpleJdbcDaoSupport implements RolDao {

    @Autowired
    public RolDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }

    @Override
    public int save(Rol rol) {

        try {
            System.out.println(rol.getName());
            getJdbcTemplate().update("INSERT INTO rol"
                    + "("
                    + "createdBy,modifiedby,dateCreated,status,deleted,presence,ownerid,name,rolparentid,description"
                    + ")VALUES(?,?,NOW(),?,?,?,?,?,?,?)", rol.getCreatedBy(), 0, 1, 0, 1, 1, rol.getName().trim(), rol.getRolParentId(), rol.getDescription().trim());

            return getSimpleJdbcTemplate().queryForInt("SELECT last_value FROM entity_id_seq");
        } catch (Exception e) {
            System.out.println("Exception-RolDaoImplSave:" + e.getMessage());
        }
        return 0;
    }

    @Override
    public int delete(Rol rol) {
        return getJdbcTemplate().update("UPDATE rol SET modifiedby = ?, datemodified = NOW(),deleted = 1 WHERE id = ?", rol.getModifiedBy(), rol.getId());
    }

    @Override
    public int update(Rol rol) {
        try {

            return getJdbcTemplate().update("UPDATE rol SET modifiedby = ?,datemodified = NOW(), name = ?,description = ? WHERE id = ?",
                    rol.getModifiedBy(), rol.getName().trim(), rol.getDescription().trim(), rol.getId());
        } catch (Exception e) {
            System.out.println("Exception-update:" + e.getMessage());
        }
        return 0;

    }

    @Override
    public List<Rol> getAll() {
        return getSimpleJdbcTemplate().query(
                "SELECT id,createdby,modifiedby, datecreated,datemodified,status,name,rolparentid,description FROM rol WHERE deleted = 0",
                new BeanPropertyRowMapper<Rol>(Rol.class));
    }

    @Override
    public List<Rol> getAllByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {

        String query = null;
        try {

            query = getLimit("SELECT id,createdby,modifiedby, datecreated,datemodified,status,name,rolparentid,description FROM rol WHERE deleted = 0", startLimit, endLimit, dataToSort, directionOfOrder);

            return getSimpleJdbcTemplate().query(query, new BeanPropertyRowMapper<Rol>(Rol.class));

        } catch (Exception e) {
            System.out.println("getAllByLimit : Exception :" + e.getMessage());
            return null;
        }
    }

    @Override
    public int getCountTotal() {
        return getJdbcTemplate().queryForInt("SELECT count(*) FROM rol WHERE deleted = 0");
    }

    @Override
    public List<Rol> searchByName(Rol rol, int satartLimit, int endLimit) {
        String query = null;
        try {
            query = getLimit("SELECT id,createdby,modifiedby,datecreated,datemodified,status,name,rolparentid,description FROM rol WHERE deleted = 0 AND upper(name) LIKE ('%'||?||'%')", satartLimit, endLimit, "name", "ASC");
            return getSimpleJdbcTemplate().query(query, new BeanPropertyRowMapper<Rol>(Rol.class), rol.getName());
        } catch (Exception e) {
            System.out.println("searchByName:Exception:" + e.getMessage());
            return null;
        }
    }

    @Override
    public Rol getById(int id) {
        try {
            //return getSimpleJdbcTemplate().queryForObject("SELECT * FROM rol WHERE deleted = 0 AND id = ?", new BeanPropertyRowMapper<Rol>(Rol.class), id);
            return getSimpleJdbcTemplate().queryForObject("SELECT id, createdby, modifiedby, datecreated, datemodified, status,"
                    + "deleted, presence, ownerid, viewedtime, name, rolparentid, description,"
                    + "vtigerrol FROM rol WHERE deleted = 0 AND id = ?", new BeanPropertyRowMapper<Rol>(Rol.class), id);
        } catch (Exception e) {
            System.out.println("Exeption-Rol-getById:" + e.getMessage());
        }
        return null;
    }

    private String getLimit(String query, int startLimit, int endLimit,
            String dataToSort, String directionOfOrder) {

        StringBuilder newQuery = new StringBuilder(query);

        newQuery.append(" ORDER BY ");
        newQuery.append(dataToSort);
        newQuery.append(" ");
        newQuery.append(directionOfOrder);
        newQuery.append(" ");
        newQuery.append("LIMIT ");
        newQuery.append(endLimit);
        newQuery.append(" OFFSET ");
        newQuery.append(startLimit);

        return newQuery.toString();
    }

    @Override
    public int getCountSearchByName(Rol rol) {
        return getJdbcTemplate().queryForInt("SELECT count(*) FROM rol WHERE deleted = 0 AND upper(name) LIKE ('%' || ? || '%')", rol.getName());
    }

    @Override
    public List<Rol> getAllByProfileId(int profileId) {
        return getSimpleJdbcTemplate().query("SELECT rol.id,rol.name FROM rol INNER JOIN rolprofile "
                + " ON rol.id = rolprofile.rolid WHERE rolprofile.profileid =  ?", new BeanPropertyRowMapper<Rol>(Rol.class), profileId);
    }

    @Override
    public List<Rol> getAllByParentId(int parentId) {
        return getSimpleJdbcTemplate().query("SELECT id,name FROM rol WHERE deleted = 0 AND rolparentid = ?", new BeanPropertyRowMapper<Rol>(Rol.class), parentId);
    }

    @Override
    public int updateTreeView(Rol rol) {
        return getJdbcTemplate().update("UPDATE rol SET modifiedby = ?, datemodified=NOW(), ownerid = ?,rolparentid=? WHERE id = ?", rol.getModifiedBy(), rol.getOwnerId(), rol.getRolParentId(), rol.getId());
    }

    @Override
    public String getTreeView() {
        return (String) getSimpleJdbcTemplate().queryForObject("SELECT treerol FROM treeviewrol", String.class);
    }

    @Override
    public int saveTreeView(int createdby, int ownerid, String treerol) {
        return getJdbcTemplate().update("INSERT INTO treeviewrol(createdby, datecreated, status, "
                + " deleted, presence, ownerid, treerol)"
                + " VALUES(?,NOW(),?, ?, ?,?,?)", createdby, 1, 0, 1, ownerid, treerol);
    }

    @Override
    public int getCountTreeView() {
        return getSimpleJdbcTemplate().queryForInt("SELECT COUNT(*) FROM treeviewrol ");
    }

    @Override
    public int getCountByParentId(int id) {
        return getSimpleJdbcTemplate().queryForInt("SELECT COUNT(*) FROM rol WHERE deleted = 0 AND id = ?", id);
    }
}
